SQL¶

https://www.ibm.com/docs/en/watson-studio-local/1.2.3?topic=notebooks-markdown-jupyter-cheatsheet

Day 1¶


Day 2¶


Day 3¶


Day 4¶


Day 5¶


SQLBOLT¶

SQL Lesson 2: Queries with constraints (Pt. 1)¶

image.png

SQL Lesson 3: Queries with constraints (Pt. 2)¶

image.png

Find All Toy Story Movies image.png

Find all movies not directed by John Lasseter image.png

Find all WALL-* movies image.png _ (underscore) is Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) col_name LIKE "AN_" (matches "AND", but not "AN")

SQL Lesson 4: Filtering and sorting Query results¶

image.png image-2.png

08-11-23 Instructor-led REVIEW of SQL¶

image.png

Subquery¶

image.png

Use of Having ?¶

The WHERE clause is used to filter rows before they are grouped and aggregated, while the HAVING clause is used to filter the results of aggregate functions applied to grouped data.

The HAVING clause is used to filter the results of aggregate functions applied to grouped data. It operates on groups of rows created using the GROUP BY clause. It's used to filter the grouped data based on aggregate results like sum, count, average, etc.

image.png

HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions

highest payment¶

image.png

image.png

Game show

  1. The Entity Relationship Diagram (ERD) are output from the following database design phase:

A. Logical Database Design - where you do your data modeling and mapping. talk about relationships between the data and general schema for the database

conceptual phase - first phase. higher ups take over and conceptualize.

physical database design - last phase. phyically build it out.

  1. First quiz of Database

Basic building block: ..... restrictions placed on the data

A. Constraint

    1. ___ describes an association among entities

B. Relationship

    1. ____ represent the data that we are collecting about the entities that we are storing about

c. entity?

ex. columns are attributes and entities are values within the columns

    1. an attribute is a charactaritstic of an entity
  1. an ___ is a business object that represents a group or category of data.

C. entity

  1. the object we collect data about is an entity. the data we collect is an

C. Where

image-2.png

A. IFNULL

image.png

A. Having (used to filter groups that specify conditions)

image.png

WE CANNOT USE THE OPERATOR '=' FOR NULL

NO TWO NULLS ARE EQUAL.

YOU MUST USE THE BUILT-IN FUNCTIONS IS NULL OR IF NULL. CANNOT USE EQUAL SIGN

THEREFORE THE OUTPUT OF THIS IS D. NO VALUES (THIS CODE IS FULLY FUNCTIONAL, SO NO SYNTAX ERROR)

THERE WILL NEVER BE A SHIPMENT DATE THAT = NULL, SO IT WILL ALWAYS RETURN NO VALUE.

image.png

C. ON Clause

image.png

image.png

b) constraint

DDL data definintion language is the SQL subsystem that does constraints

image.png

B) filters rows that are not included in a specified set of values

image.png

image.png

B) defines a unique identifier for each row in the table

best practice charactaristics of a primary key = unique, not null, best if auto increments, best if its numeric,

image.png

C. Aggregate (aggregate functions perform calculations based on the GROUP BY clause to aggregte things like SUM(), AVG())

image.png

image.png

Join

image.png

A) Distinct

image.png

D) Ensure the integrity of database operations as a single, automic unit.

image.png

B) UPDATE (you update records)

Alter (to alter the schema)

image.png

B) ADD

INSERT INTO is an alternate clause to add whole new rows while specifying values

In summary, INSERT INTO is used to add new rows with data to an existing table, while ADD is used within an ALTER TABLE statement to add new columns to an existing table's structure. They serve different purposes in manipulating the database schema and data.

image.png

image.png

ON KBA

KNOW WHAT A CROSS JOIN IS FOR KBA

AND KNOW WHAT CARTESIAN IS FOR KBA